//
//  CommonDB.m
//  CommonDB
//
//  Created by Thuan on 6/4/14.
//  Copyright (c) 2014 Thuan. All rights reserved.
//

#import "CommonDB.h"
#import "Scores.h"

@implementation CommonDB
-(BOOL) open {
    NSString *docsDir;
    NSArray *dirPaths;
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    //NSString *documentFilePath = [documentPath stringByAppendingPathComponent:@"notes.sqlite"];
    //count++;
    //returnCode = sqlite3_open([documentFilePath UTF8String], &pDatabase);
    // Build the path to the database file
    NSString*databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent:@"notes.sqlite"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &pDatabase) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt =
            "CREATE TABLE scores (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT, score INTEGER)";
            if (sqlite3_exec(pDatabase, sql_stmt, NULL, NULL, &errMsg)
                != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(pDatabase);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
    }
    else
    {
       returnCode = sqlite3_open([databasePath UTF8String], &pDatabase);
        isSuccess = YES;
    }
    return isSuccess;
    //NSArray* arrPaths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    //NSString* documentPath = [arrPaths objectAtIndex:0];
    
}
-(void) close {
    count--;
    NSLog(@"Close database: %d", count);
    sqlite3_close(pDatabase);
}
-(void) excuteQuery:(NSString*)sql {
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(pDatabase, [sql UTF8String], -1, &stmt, NULL);
    if(sqlite3_step(stmt)==SQLITE_DONE)
    {
    }
    else
    {
        NSLog(@"Error: %@",[NSString stringWithUTF8String:(const char*)sqlite3_errmsg(pDatabase)]);
    }
    sqlite3_finalize(stmt);
}

-(NSMutableArray*)getAllScores
{
    NSMutableArray*rs=[[NSMutableArray alloc]init];
    if(returnCode==SQLITE_OK)
    {
        NSString*conditon=@"";
        NSString*sql=[NSString stringWithFormat:@"SELECT * FROM scores ORDER BY score DESC%@",conditon];
        sqlite3_stmt *stmt;
        if(sqlite3_prepare_v2(pDatabase, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                Scores*n=[[Scores alloc] init];
                n.idScore = [[NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 0)] intValue];
                n.namePlayer = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 1)];
                n.score = [[NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 2)] intValue];
                [rs addObject:n];
            }
        } else {
             NSLog(@"Error: %@",[NSString stringWithUTF8String:(const char*)sqlite3_errmsg(pDatabase)]);
        }
        sqlite3_finalize(stmt);
    }
    [self close];
    return rs;
}
- (int) insertScore: (Scores*) score {
    sqlite3_stmt *stmt=nil;
    const char *sql = "INSERT INTO scores(name,score) VALUES(?,?)";
    
    sqlite3_prepare_v2(pDatabase, sql, -1, &stmt, NULL);
    sqlite3_bind_text(stmt, 1, [score.namePlayer UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [[NSString stringWithFormat:@"%d",score.score] UTF8String], -1, SQLITE_TRANSIENT);
    if(sqlite3_step(stmt)==SQLITE_DONE)
    {
        score.idScore = sqlite3_last_insert_rowid(pDatabase);
    }
    else
    {
        NSLog(@"Error insert event: %@",[NSString stringWithUTF8String:(const char*)sqlite3_errmsg(pDatabase)]);
    }
    sqlite3_finalize(stmt);
    return score.idScore;
}

+(CommonDB*) shared {
    static CommonDB* instance;
    if (instance == nil) {
        instance = [[CommonDB alloc] init];
    }
    return instance;
}
@end
